import sqlite3
#添加题库
def question_add(question,answer):
    import sqlite3
    # 创建数据库连接
    conn = sqlite3.connect('tables.db')

    # 创建游标
    cursor = conn.cursor()
    #输入题目和答案

    # 插入题目和答案到题库表
    cursor.execute("INSERT INTO questions (question, answer) VALUES (?, ?)", (question, answer))
    # 提交更改
    conn.commit()

    # 关闭游标和连接
    cursor.close()
    conn.close()

#读取题库
def question_read():
    import sqlite3
    # 创建数据库连接
    conn = sqlite3.connect('tables.db')

    # 创建游标
    cursor = conn.cursor()

    # 查询并打印题库表中的题目和答案
    cursor.execute("SELECT question, answer FROM questions")
    question_info = cursor.fetchall()
    for row in question_info:
        question, answer = row
        print(f"Question: {question}, Answer: {answer}")
        print("------------------------")

        # 提交更改
    conn.commit()

    # 关闭游标和连接
    cursor.close()
    conn.close()

#添加试卷
def papers_add(class_id, paper_title, selected_questions):
    import sqlite3
    # 创建数据库连接
    conn = sqlite3.connect('tables.db')

    # 创建游标
    cursor = conn.cursor()

    # 插入一个试卷，同时勾选相关题目到试卷中
    cursor.execute("INSERT INTO papers (title, class_id) VALUES (?, ?)", (paper_title, class_id))  # 插入试卷信息
    conn.commit()  # 提交事务
    paper_id = cursor.lastrowid

    # 将选择的题目与试卷关联起来
    for question_id in selected_questions:
        cursor.execute("INSERT INTO paper_questions (paper_id, question_id) VALUES (?, ?)", (paper_id, question_id))

        # 提交更改
    conn.commit()

    # 关闭游标和连接
    cursor.close()
    conn.close()

#读取试卷
def papers_read():
    import sqlite3
    # 创建数据库连接
    conn = sqlite3.connect('tables.db')

    # 创建游标
    cursor = conn.cursor()

    # 查询并打印与指定试卷相关联的所有题目和答案
    paper_id = 1  # 获取刚刚插入的试卷ID
    cursor.execute(
        "SELECT q.question, q.answer FROM questions q JOIN paper_questions p ON q.id = p.question_id WHERE p.paper_id = ?",
        (paper_id,))  # 检索与试卷相关联的所有题目信息
    paper_questions = cursor.fetchall()  # 获取查询结果
    for row in paper_questions:
        question, answer = row
        print(f"Paper Question: {question}, Answer: {answer}")  # 打印题目和答案信息
        print("------------------------")  # 打印分隔线

        # 提交更改
    conn.commit()

    # 关闭游标和连接
    cursor.close()
    conn.close()


#该班级试卷对应的内容
def get_papers_by_class(class_id):
    # 创建数据库连接
    import sqlite3
    conn = sqlite3.connect('tables.db')
    cursor = conn.cursor()

    # 查询给定班级的所有试卷
    cursor.execute(
        'SELECT p.title, q.question, q.answer, pq.paper_id, q.id FROM papers p INNER JOIN paper_questions pq ON p.id = pq.paper_id INNER JOIN questions q ON q.id = pq.question_id WHERE p.class_id = ?',
        (class_id,))
    papers = cursor.fetchall()  # 获取所有试卷内容

    # 使用字典将相同标题的试卷整合在一起
    papers_dict = {}
    for paper in papers:
        title, question, answer, paper_id, question_id = paper
        if title in papers_dict:
            papers_dict[title].append((question, answer))
        else:
            papers_dict[title] = [(question, answer)]
            # 关闭游标和连接
    cursor.close()
    conn.close()
    print(papers_dict)  # 输出整合后的试卷字典
    return papers_dict
# 获取所有试卷名对应的题目ID
def get_paper_title_and_question_id(class_id):
    conn = sqlite3.connect('tables.db')  # 连接到数据库
    cursor = conn.cursor()  # 创建游标

    # 查询给定班级的所有试卷名和题目ID
    cursor.execute(
        'SELECT p.title, q.id FROM papers p INNER JOIN paper_questions pq ON p.id = pq.paper_id INNER JOIN questions q ON q.id = pq.question_id WHERE p.class_id = ?',
        (class_id,))
    rows = cursor.fetchall()  # 获取所有试卷名和题目ID

    # 将结果组织成字典形式
    result = {}
    for row in rows:
        title = row[0]
        question_id = row[1]
        if title not in result:
            result[title] = []
        result[title].append(question_id)
        # 关闭游标和连接
    cursor.close()
    conn.close()
    print(result)
    return result






if __name__ == '__main__':

    try:
        get_papers_by_class('2')

    except rospy.ROSInterruptException:
    	pass